package alpaca.database;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import alpaca.core.Contact;
import alpaca.core.ContactList;
import alpaca.core.IUser;
import alpaca.core.Message;
import alpaca.core.MessageList;
import alpaca.core.ORMLiteUser;
import alpaca.core.UserContact;
import alpaca.core.UserList;
import alpaca.util.PasswordEncrypter;

import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.field.DataType;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.stmt.Where;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

/**
 * ORMLiteDatabaseSystem maps rows in the database to objects.
 * System uses annotations in the classes User, UserContact and Message.
 * A DataManagerObject (DAO) is created for each class that handles all
 * communication with the database. 
 */
public class ORMLiteDatabaseSystem implements IDatabaseSystem {

	private ConnectionSource conn;

	@Override
	public void createConnection(String db, String user, String pass) throws SQLException {
		conn = new JdbcConnectionSource(db, user, pass);
	}
	
	
	@Override
	public void createTables() throws SQLException {
		Dao<ORMLiteUser,Integer> userDao = DaoManager.createDao(conn, ORMLiteUser.class);
		if (!userDao.isTableExists()) {
			TableUtils.createTable(conn, ORMLiteUser.class);
		}
		
		Dao<UserContact,Integer> contactDao = DaoManager.createDao(conn, UserContact.class);
		if (!contactDao.isTableExists()) {
			TableUtils.createTable(conn, UserContact.class);
		}
		
		Dao<Message,Integer> messageDao = DaoManager.createDao(conn, Message.class);
		if (!messageDao.isTableExists()) {
			TableUtils.createTable(conn, Message.class);
		}
		

	}

	@Override
	public void closeConnection() throws SQLException {
		conn.close();
	}
	
	@Override
	public ORMLiteUser getUserByEmail(String email) throws SQLException {
		Dao<ORMLiteUser,Integer> dao = DaoManager.createDao(conn, ORMLiteUser.class);
		QueryBuilder<ORMLiteUser,Integer> queryBuilder = dao.queryBuilder();
		queryBuilder.selectColumns("firstName", "lastName", "email", "active");
		queryBuilder.where().eq("email", email)
			.and().eq("active", true);
		return dao.queryForFirst(queryBuilder.prepare());
	}
	
	
	@Override
	public UserList getUserList() throws SQLException {
		Dao<ORMLiteUser,Integer> dao = DaoManager.createDao(conn, ORMLiteUser.class);
		return new UserList(dao.queryForAll());
	}
	
	@Override
	public UserList getUserList(String term) throws SQLException {
		Dao<ORMLiteUser,Integer> dao = DaoManager.createDao(conn, ORMLiteUser.class);
		
		QueryBuilder<ORMLiteUser,Integer> queryBuilder = dao.queryBuilder();
		Where<ORMLiteUser,Integer> where = queryBuilder.where();
		where.and(
			where.eq("active", true),
			where
				.like("email", "%"+term+"%")
				.or().like("firstName", "%"+term+"%")
				.or().like("lastName", "%"+term+"%"));

		return new UserList(dao.query(queryBuilder.prepare()));
	}
	
	@Override
	public UserList getUsersWithContact(IUser iUser) throws SQLException {
		if(!(iUser instanceof ORMLiteUser)) {
			throw new IllegalArgumentException();
		}
		
		ORMLiteUser user = (ORMLiteUser) iUser;
		
		Dao<UserContact,Integer> contactDao = DaoManager.createDao(conn, UserContact.class);
		Dao<ORMLiteUser,Integer> userDao = DaoManager.createDao(conn, ORMLiteUser.class);
		UserList userList = new UserList();
		
		String query = "SELECT userId FROM contacts WHERE contactId = ? GROUP BY userId";
		DataType[] dataType = new DataType[1];
		dataType[0] = DataType.INTEGER;
		GenericRawResults<Object[]> results = contactDao.queryRaw(query, dataType, Integer.toString(user.getId()));
		for (Object[] row : results.getResults()) {
			userList.add(userDao.queryForId((Integer)row[0]));
		}
		return userList;
	}
	
	@Override
	public ContactList getContactList(IUser iUser) throws SQLException {
		if(!(iUser instanceof ORMLiteUser)) {
			throw new IllegalArgumentException();
		}
		
		ORMLiteUser user = (ORMLiteUser) iUser;
		
		Dao<UserContact,Integer> contactDao = DaoManager.createDao(conn, UserContact.class);
		
		ContactList contactList = new ContactList();
		QueryBuilder<UserContact,Integer> queryBuilder = contactDao.queryBuilder();
		queryBuilder.where().eq("userId", user.getId());
		List<UserContact> userContactList = contactDao.query(queryBuilder.prepare());
		
		if (userContactList.size() > 0) {
			contactList.setOwner(user);
			contactList.setContacts(userContactList);
		}
		return contactList;
	}
	
	@Override
	public boolean checkPassword(IUser user, String password)
			throws SQLException {
		Dao<ORMLiteUser,Integer> dao = DaoManager.createDao(conn, ORMLiteUser.class);
		QueryBuilder<ORMLiteUser,Integer> queryBuilder = dao.queryBuilder();
		queryBuilder.where().eq("email", user.getEmail())
		.and()
		.eq("password", PasswordEncrypter.encrypt(password));
		if (dao.queryForFirst(queryBuilder.prepare()) != null) {
			return true;
		}
		return false;
	}
	
	@Override
	public MessageList getLogMessages(IUser iFrom, IUser iTo, Date start, Date stop) throws SQLException {

		if((!(iFrom instanceof ORMLiteUser) && iFrom != null) || (!(iTo instanceof ORMLiteUser) && iTo != null)) {
			throw new IllegalArgumentException();
		}
		
		ORMLiteUser to = (ORMLiteUser) iTo;
		ORMLiteUser from = (ORMLiteUser) iFrom;
		
		Dao<Message,Integer> msgDao = DaoManager.createDao(conn, Message.class);
		Dao<ORMLiteUser,Integer> userDao = DaoManager.createDao(conn, ORMLiteUser.class);
		
		SimpleDateFormat format = new SimpleDateFormat();
		format.applyPattern("yyyy-MM-dd HH:mm:ss");
		
		List<String> params = new LinkedList<String>();
		if (from != null) params.add("fromId = " + from.getId());
		if (to != null) params.add("toId = " + to.getId());
		if (start != null) params.add("date > '" + format.format(start) + "'");
		if (stop != null) params.add("date < '" + format.format(stop) + "'");
		
		StringBuilder query = new StringBuilder("SELECT message,fromId,toId,date FROM logs");
		if (params.size() > 0) {
			query.append(" WHERE " + params.get(0)); 
		}
		for (int i=1; i<params.size(); i++) {
			query.append(" AND " + params.get(i));
		}
		query.append(" AND sent = 1");
//		
//		if (from != null) { 
//			where.eq("fromId", from.getId());
//		}
//		if (to != null) {
//			if (from != null) {
//				where.and();
//			}
//			where.eq("toId", to.getId());
//		}
//		return new MessageList(dao.query(queryBuilder.prepare()));
		MessageList messageList = new MessageList();
		List<String[]> results = msgDao.queryRaw(query.toString(), new String[0]).getResults();
		for (String[] result : results) {
			Message msg = null;
			try {
				msg = new Message(result[0], 
						userDao.queryForId(Integer.parseInt(result[1])),
						userDao.queryForId(Integer.parseInt(result[2])),
						format.parse(result[3]));
			} catch (NumberFormatException e) {
				e.printStackTrace();
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if (msg != null) {
				messageList.add(msg);
			}
		}
		return messageList;
	}
	
	@Override
	public MessageList getOfflineMessages(IUser iUser) throws SQLException {	
		if(!(iUser instanceof ORMLiteUser)) {
			throw new IllegalArgumentException();
		}
		
		ORMLiteUser user = (ORMLiteUser) iUser;
		
		Dao<Message,Integer> msgDao = DaoManager.createDao(conn, Message.class);
		QueryBuilder<Message, Integer> queryBuilder = msgDao.queryBuilder();
		Where<Message,Integer> where = queryBuilder.where();
		where.and(
			where.eq("sent", false),
			where.eq("toId", user.getId()));
		
		MessageList messageList = new MessageList(msgDao.query(queryBuilder.prepare()));
		UpdateBuilder<Message, Integer> updateBuilder = msgDao.updateBuilder();
		updateBuilder.setWhere(where);
		updateBuilder.updateColumnValue("sent", true);
		msgDao.update(updateBuilder.prepare());

		return messageList;

	}
	
	@Override
	public void createUser(String firstName, String lastName, String email, String password) throws SQLException {

		ORMLiteUser user = new ORMLiteUser(firstName, lastName, email, PasswordEncrypter.encrypt(password));
		Dao<ORMLiteUser,Integer> dao = DaoManager.createDao(conn, ORMLiteUser.class);
		dao.create(user);
	}
	
	@Override
	public void createContact(IUser iUser, Contact contact) throws SQLException {
		if(!(iUser instanceof ORMLiteUser) || !(contact.getUser() instanceof ORMLiteUser)) {
			throw new IllegalArgumentException();
		}
		
		ORMLiteUser user = (ORMLiteUser) iUser;
		ORMLiteUser contactUser = (ORMLiteUser) contact.getUser();
		
		Dao<UserContact,Integer> contactDao = DaoManager.createDao(conn, UserContact.class);
		UserContact userContact = new UserContact(user, contactUser, contact.getTag());
		
		contactDao.create(userContact);
	}

	@Override
	public void addLogMessage(Message message) throws SQLException {
		Dao<Message,Integer> dao = DaoManager.createDao(conn, Message.class);
		dao.create(message);
	}
	
	@Override
	public void updateUser(IUser user, String firstName, String lastName, String email, String password, boolean active) throws SQLException {
		Dao<ORMLiteUser,Integer> dao = DaoManager.createDao(conn, ORMLiteUser.class);
		UpdateBuilder<ORMLiteUser, Integer> updateBuilder =
			dao.updateBuilder();
		
		updateBuilder.where().eq("email", user.getEmail());
		
		updateBuilder.updateColumnValue("firstName", firstName);
		updateBuilder.updateColumnValue("lastName", lastName);
		updateBuilder.updateColumnValue("email", email);
		updateBuilder.updateColumnValue("active", active);
		
		if (!password.isEmpty()){
			updateBuilder.updateColumnValue("password", PasswordEncrypter.encrypt(password));
		}

		dao.update(updateBuilder.prepare());
	}
	
	@Override
	public void updateTag(IUser iUser, Contact contact) throws SQLException {
		if(!(iUser instanceof ORMLiteUser) || !(contact.getUser() instanceof ORMLiteUser)) {
			throw new IllegalArgumentException();
		}
		
		ORMLiteUser user = (ORMLiteUser) iUser;
		ORMLiteUser contactUser = (ORMLiteUser) contact.getUser();
		
		Dao<UserContact,Integer> contactDao = DaoManager.createDao(conn, UserContact.class);
		UpdateBuilder<UserContact,Integer> updateBuilder = contactDao.updateBuilder();
		
		updateBuilder.where().eq("userId", user.getId())
			.and().eq("contactId", contactUser.getId());
		
		updateBuilder.updateColumnValue("tag", contact.getTag());
		
		contactDao.update(updateBuilder.prepare());
	}	

	@Override
	public void deleteUser(IUser iUser) throws SQLException {
		Dao<ORMLiteUser,Integer> dao = DaoManager.createDao(conn, ORMLiteUser.class);
		UpdateBuilder<ORMLiteUser, Integer> updateBuilder =
			dao.updateBuilder();
		
		updateBuilder.where().eq("email", iUser.getEmail());
		updateBuilder.updateColumnValue("active", false);
		
		dao.update(updateBuilder.prepare());
	}

	@Override
	public void removeContact(IUser iCurrentUser, IUser iContactUser) throws SQLException {
		if(!(iCurrentUser instanceof ORMLiteUser) || !(iContactUser instanceof ORMLiteUser)) {
			throw new IllegalArgumentException();
		}
		
		ORMLiteUser user = (ORMLiteUser) iCurrentUser;
		ORMLiteUser contactUser = (ORMLiteUser) iContactUser;
		
		Dao<UserContact,Integer> contactDao = DaoManager.createDao(conn, UserContact.class);
		DeleteBuilder<UserContact,Integer> deleteBuilder = contactDao.deleteBuilder();
		deleteBuilder.where().eq("userId", user.getId())
			.and().eq("contactId", contactUser.getId());
		contactDao.delete(deleteBuilder.prepare());
	}



}
